import tkinter as tk
from tkinter import ttk, messagebox, scrolledtext
import sqlite3
from datetime import datetime
import os


class BookOrderSystem:
    def __init__(self, root):
        self.root = root
        self.root.title("图书订购系统")
        self.root.geometry("1000x700")
        self.root.configure(bg="#f0f8ff")

        # 初始化数据库
        self.init_database()

        # 创建登录界面
        self.create_login_interface()

    def init_database(self):
        # 创建数据库连接
        self.conn = sqlite3.connect('book_orders.db')
        self.cursor = self.conn.cursor()

        # 创建订户表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS subscribers (
            subscriber_id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            phone TEXT NOT NULL,
            email TEXT NOT NULL,
            address TEXT NOT NULL
        )
        ''')

        # 创建图书表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS books (
            isbn TEXT PRIMARY KEY,
            title TEXT NOT NULL,
            publisher TEXT NOT NULL,
            price REAL NOT NULL,
            stock INTEGER NOT NULL
        )
        ''')

        # 创建订单表
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            order_id INTEGER PRIMARY KEY AUTOINCREMENT,
            subscriber_id TEXT NOT NULL,
            isbn TEXT NOT NULL,
            quantity INTEGER NOT NULL,
            order_date TEXT NOT NULL,
            FOREIGN KEY (subscriber_id) REFERENCES subscribers(subscriber_id),
            FOREIGN KEY (isbn) REFERENCES books(isbn)
        )
        ''')

        # 插入示例数据
        self.insert_sample_data()

        self.conn.commit()

    def insert_sample_data(self):
        # 检查数据是否存在
        self.cursor.execute("SELECT COUNT(*) FROM subscribers")
        if self.cursor.fetchone()[0] == 0:
            # 插入订户数据
            subscribers = [
                ('S235010413', '李兆辉', '13800138000', 'lzh@example.com', '北京市海淀区中关村大街1号'),
                ('S20230001', '张三', '13900139000', 'zhangsan@example.com', '上海市浦东新区张江高科技园区'),
                ('S20230002', '李四', '13700137000', 'lisi@example.com', '广州市天河区珠江新城')
            ]
            self.cursor.executemany('''
            INSERT INTO subscribers (subscriber_id, name, phone, email, address)
            VALUES (?, ?, ?, ?, ?)
            ''', subscribers)

            # 插入图书数据
            books = [
                ('9787111636664', 'Python编程：从入门到实践', '人民邮电出版社', 89.00, 50),
                ('9787302517599', '机器学习', '清华大学出版社', 128.00, 30),
                ('9787115516018', '深入理解计算机系统', '机械工业出版社', 139.00, 25),
                ('9787121384101', '算法导论', '电子工业出版社', 128.00, 40),
                ('9787302455106', '数据库系统概念', '清华大学出版社', 99.00, 35),
                ('9787115485583', '计算机网络：自顶向下方法', '机械工业出版社', 89.00, 20),
                ('9787302423280', '操作系统概念', '清华大学出版社', 98.00, 15),
                ('9787115275790', 'JavaScript高级程序设计', '人民邮电出版社', 129.00, 45)
            ]
            self.cursor.executemany('''
            INSERT INTO books (isbn, title, publisher, price, stock)
            VALUES (?, ?, ?, ?, ?)
            ''', books)

            # 插入订单数据
            orders = [
                ('S235010413', '9787111636664', 2, '2023-05-10'),
                ('S235010413', '9787302455106', 1, '2023-05-15'),
                ('S20230001', '9787121384101', 1, '2023-05-12')
            ]
            self.cursor.executemany('''
            INSERT INTO orders (subscriber_id, isbn, quantity, order_date)
            VALUES (?, ?, ?, ?)
            ''', orders)

            self.conn.commit()

    def create_login_interface(self):
        # 清除现有界面
        for widget in self.root.winfo_children():
            widget.destroy()

        # 创建登录界面
        self.root.title("图书订购系统 - 登录")

        # 创建主框架
        main_frame = ttk.Frame(self.root, padding=20)
        main_frame.pack(fill=tk.BOTH, expand=True)
        main_frame.configure(style='Main.TFrame')

        # 标题
        title_label = ttk.Label(main_frame, text="图书订购系统", font=("微软雅黑", 24, "bold"), foreground="#2c3e50")
        title_label.pack(pady=30)

        # 登录表单框架
        form_frame = ttk.Frame(main_frame, padding=20)
        form_frame.pack(fill=tk.X, padx=100, pady=20)
        form_frame.configure(style='Form.TFrame')

        # 订户代号标签和输入框
        ttk.Label(form_frame, text="订户代号:", font=("微软雅黑", 12)).grid(row=0, column=0, padx=10, pady=10,
                                                                            sticky=tk.E)
        self.subscriber_id_entry = ttk.Entry(form_frame, font=("微软雅黑", 12))
        self.subscriber_id_entry.grid(row=0, column=1, padx=10, pady=10, sticky=tk.W)

        # 登录按钮
        login_button = ttk.Button(form_frame, text="登录", command=self.login, style='Accent.TButton')
        login_button.grid(row=1, column=0, columnspan=2, pady=20)

        # 样式配置
        self.root.style = ttk.Style()
        self.root.style.configure('Main.TFrame', background='#f0f8ff')
        self.root.style.configure('Form.TFrame', background='#e3f2fd', borderwidth=2, relief="groove")
        self.root.style.configure('Accent.TButton', font=("微软雅黑", 12), background='#4a90e2', foreground='white')
        self.root.style.map('Accent.TButton', background=[('active', '#3a7bd5')])

        # 设置焦点
        self.subscriber_id_entry.focus_set()

    def login(self):
        subscriber_id = self.subscriber_id_entry.get().strip()
        if not subscriber_id:
            messagebox.showerror("错误", "请输入订户代号")
            return

        # 检查订户是否存在
        self.cursor.execute("SELECT * FROM subscribers WHERE subscriber_id=?", (subscriber_id,))
        subscriber = self.cursor.fetchone()

        if subscriber:
            self.current_subscriber = {
                'id': subscriber[0],
                'name': subscriber[1],
                'phone': subscriber[2],
                'email': subscriber[3],
                'address': subscriber[4]
            }
            self.create_main_interface()
        else:
            messagebox.showerror("错误", "订户代号不存在")

    def create_main_interface(self):
        # 清除现有界面
        for widget in self.root.winfo_children():
            widget.destroy()

        # 设置主窗口标题
        self.root.title(f"图书订购系统 - 欢迎 {self.current_subscriber['name']}")

        # 创建主框架
        main_frame = ttk.Frame(self.root)
        main_frame.pack(fill=tk.BOTH, expand=True)

        # 创建选项卡
        self.tab_control = ttk.Notebook(main_frame)

        # 创建三个选项卡
        self.my_info_tab = ttk.Frame(self.tab_control)
        self.book_search_tab = ttk.Frame(self.tab_control)
        self.my_orders_tab = ttk.Frame(self.tab_control)

        self.tab_control.add(self.my_info_tab, text='我的信息')
        self.tab_control.add(self.book_search_tab, text='书目查询')
        self.tab_control.add(self.my_orders_tab, text='我的订单')

        self.tab_control.pack(expand=1, fill="both", padx=10, pady=10)

        # 构建各个选项卡的内容
        self.build_my_info_tab()
        self.build_book_search_tab()
        self.build_my_orders_tab()

        # 添加退出按钮
        logout_button = ttk.Button(main_frame, text="退出系统", command=self.logout)
        logout_button.pack(side=tk.BOTTOM, pady=10)

    def build_my_info_tab(self):
        # 信息显示框架
        info_frame = ttk.LabelFrame(self.my_info_tab, text="个人信息", padding=15)
        info_frame.pack(fill=tk.BOTH, expand=True, padx=20, pady=20)

        # 创建标签和输入框
        fields = [
            ("订户代号:", "id", True),
            ("姓名:", "name", False),
            ("手机号:", "phone", False),
            ("电子邮件:", "email", False),
            ("地址:", "address", False)
        ]

        self.info_entries = {}
        for i, (label, field, readonly) in enumerate(fields):
            ttk.Label(info_frame, text=label, font=("微软雅黑", 11)).grid(row=i, column=0, padx=10, pady=10,
                                                                          sticky=tk.E)

            if readonly:
                entry = ttk.Entry(info_frame, font=("微软雅黑", 11))
                entry.insert(0, self.current_subscriber[field])
                entry.config(state="readonly")
            else:
                entry = ttk.Entry(info_frame, font=("微软雅黑", 11))
                entry.insert(0, self.current_subscriber[field])
                self.info_entries[field] = entry

            entry.grid(row=i, column=1, padx=10, pady=10, sticky=tk.W + tk.E)

        # 添加更新按钮
        update_button = ttk.Button(info_frame, text="更新信息", command=self.update_subscriber_info)
        update_button.grid(row=len(fields), column=0, columnspan=2, pady=20)

    def update_subscriber_info(self):
        # 获取输入的值
        updated_info = {
            'name': self.info_entries['name'].get(),
            'phone': self.info_entries['phone'].get(),
            'email': self.info_entries['email'].get(),
            'address': self.info_entries['address'].get()
        }

        # 验证输入
        if not all(updated_info.values()):
            messagebox.showerror("错误", "所有字段都必须填写")
            return

        # 更新数据库
        try:
            self.cursor.execute('''
            UPDATE subscribers 
            SET name=?, phone=?, email=?, address=?
            WHERE subscriber_id=?
            ''', (
                updated_info['name'],
                updated_info['phone'],
                updated_info['email'],
                updated_info['address'],
                self.current_subscriber['id']
            ))
            self.conn.commit()

            # 更新当前用户信息
            self.current_subscriber.update(updated_info)
            messagebox.showinfo("成功", "个人信息更新成功")
        except Exception as e:
            messagebox.showerror("错误", f"更新失败: {str(e)}")

    def build_book_search_tab(self):
        # 搜索框架
        search_frame = ttk.Frame(self.book_search_tab)
        search_frame.pack(fill=tk.X, padx=20, pady=10)

        ttk.Label(search_frame, text="搜索书名:", font=("微软雅黑", 11)).pack(side=tk.LEFT, padx=(0, 10))

        self.search_entry = ttk.Entry(search_frame, font=("微软雅黑", 11), width=40)
        self.search_entry.pack(side=tk.LEFT, padx=(0, 10), fill=tk.X, expand=True)

        search_button = ttk.Button(search_frame, text="搜索", command=self.search_books)
        search_button.pack(side=tk.LEFT)

        # 图书表格框架
        table_frame = ttk.Frame(self.book_search_tab)
        table_frame.pack(fill=tk.BOTH, expand=True, padx=20, pady=10)

        # 创建表格
        columns = ("isbn", "title", "publisher", "price", "stock", "action")
        self.book_tree = ttk.Treeview(
            table_frame,
            columns=columns,
            show="headings",
            selectmode="browse",
            height=15
        )

        # 设置列
        self.book_tree.heading("isbn", text="ISBN")
        self.book_tree.heading("title", text="书名")
        self.book_tree.heading("publisher", text="出版社")
        self.book_tree.heading("price", text="价格")
        self.book_tree.heading("stock", text="库存")
        self.book_tree.heading("action", text="操作")

        # 设置列宽
        self.book_tree.column("isbn", width=150)
        self.book_tree.column("title", width=250)
        self.book_tree.column("publisher", width=150)
        self.book_tree.column("price", width=80, anchor=tk.CENTER)
        self.book_tree.column("stock", width=80, anchor=tk.CENTER)
        self.book_tree.column("action", width=100, anchor=tk.CENTER)

        # 添加滚动条
        scrollbar = ttk.Scrollbar(table_frame, orient=tk.VERTICAL, command=self.book_tree.yview)
        self.book_tree.configure(yscroll=scrollbar.set)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
        self.book_tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)

        # 绑定事件
        self.book_tree.bind("<ButtonRelease-1>", self.on_book_tree_click)

        # 加载图书数据
        self.search_books()

    def search_books(self):
        # 清空现有数据
        for item in self.book_tree.get_children():
            self.book_tree.delete(item)

        # 获取搜索关键词
        keyword = self.search_entry.get().strip()

        # 查询数据库
        if keyword:
            self.cursor.execute('''
            SELECT isbn, title, publisher, price, stock 
            FROM books 
            WHERE title LIKE ? OR publisher LIKE ? OR isbn LIKE ?
            ''', (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        else:
            self.cursor.execute('SELECT isbn, title, publisher, price, stock FROM books')

        books = self.cursor.fetchall()

        # 添加到表格
        for book in books:
            stock = book[4]
            # 根据库存设置操作按钮状态
            if stock > 0:
                action = "订购"
            else:
                action = "缺货"

            self.book_tree.insert("", tk.END, values=(
                book[0], book[1], book[2], f"¥{book[3]:.2f}", stock, action
            ))

    def on_book_tree_click(self, event):
        # 获取点击的行
        region = self.book_tree.identify("region", event.x, event.y)
        if region != "cell":
            return

        column = self.book_tree.identify_column(event.x)
        if column == "#6":  # 操作列
            item_id = self.book_tree.focus()
            item = self.book_tree.item(item_id)
            values = item["values"]

            if values[5] == "订购":  # 库存大于0
                self.order_book(values[0], values[1])

    def order_book(self, isbn, title):
        # 创建订购对话框
        order_dialog = tk.Toplevel(self.root)
        order_dialog.title(f"订购图书 - {title}")
        order_dialog.geometry("400x250")
        order_dialog.resizable(False, False)
        order_dialog.transient(self.root)
        order_dialog.grab_set()

        # 对话框内容
        ttk.Label(order_dialog, text=f"图书: {title}", font=("微软雅黑", 12)).pack(pady=(20, 10))

        # 获取当前库存
        self.cursor.execute("SELECT stock FROM books WHERE isbn=?", (isbn,))
        stock = self.cursor.fetchone()[0]

        ttk.Label(order_dialog, text=f"当前库存: {stock}", font=("微软雅黑", 11)).pack(pady=5)

        # 订购数量
        quantity_frame = ttk.Frame(order_dialog)
        quantity_frame.pack(pady=10)

        ttk.Label(quantity_frame, text="订购数量:", font=("微软雅黑", 11)).pack(side=tk.LEFT, padx=5)

        quantity_var = tk.IntVar(value=1)
        quantity_spinbox = ttk.Spinbox(
            quantity_frame,
            from_=1,
            to=min(10, stock),
            textvariable=quantity_var,
            width=5,
            font=("微软雅黑", 11)
        )
        quantity_spinbox.pack(side=tk.LEFT, padx=5)

        # 按钮框架
        button_frame = ttk.Frame(order_dialog)
        button_frame.pack(pady=20)

        def place_order():
            quantity = quantity_var.get()
            if quantity < 1:
                messagebox.showerror("错误", "订购数量必须大于0")
                return

            # 检查库存是否足够
            if quantity > stock:
                messagebox.showerror("错误", "库存不足")
                return

            # 创建订单
            try:
                order_date = datetime.now().strftime("%Y-%m-%d")
                self.cursor.execute('''
                INSERT INTO orders (subscriber_id, isbn, quantity, order_date)
                VALUES (?, ?, ?, ?)
                ''', (self.current_subscriber['id'], isbn, quantity, order_date))

                # 更新库存
                self.cursor.execute('''
                UPDATE books SET stock = stock - ? WHERE isbn = ?
                ''', (quantity, isbn))

                self.conn.commit()

                messagebox.showinfo("成功", f"成功订购 {quantity} 本《{title}》")
                order_dialog.destroy()

                # 刷新图书列表和订单列表
                self.search_books()
                self.load_orders()
            except Exception as e:
                messagebox.showerror("错误", f"订购失败: {str(e)}")

        ttk.Button(button_frame, text="确认订购", command=place_order).pack(side=tk.LEFT, padx=10)
        ttk.Button(button_frame, text="取消", command=order_dialog.destroy).pack(side=tk.LEFT, padx=10)

    def build_my_orders_tab(self):
        # 订单表格框架
        table_frame = ttk.Frame(self.my_orders_tab)
        table_frame.pack(fill=tk.BOTH, expand=True, padx=20, pady=20)

        # 创建表格
        columns = ("order_id", "order_date", "title", "quantity", "price", "total")
        self.order_tree = ttk.Treeview(
            table_frame,
            columns=columns,
            show="headings",
            selectmode="browse",
            height=15
        )

        # 设置列
        self.order_tree.heading("order_id", text="订单号")
        self.order_tree.heading("order_date", text="日期")
        self.order_tree.heading("title", text="图书名称")
        self.order_tree.heading("quantity", text="数量")
        self.order_tree.heading("price", text="单价")
        self.order_tree.heading("total", text="总价")

        # 设置列宽
        self.order_tree.column("order_id", width=80, anchor=tk.CENTER)
        self.order_tree.column("order_date", width=100, anchor=tk.CENTER)
        self.order_tree.column("title", width=250)
        self.order_tree.column("quantity", width=80, anchor=tk.CENTER)
        self.order_tree.column("price", width=80, anchor=tk.CENTER)
        self.order_tree.column("total", width=100, anchor=tk.CENTER)

        # 添加滚动条
        scrollbar = ttk.Scrollbar(table_frame, orient=tk.VERTICAL, command=self.order_tree.yview)
        self.order_tree.configure(yscroll=scrollbar.set)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
        self.order_tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)

        # 加载订单数据
        self.load_orders()

    def load_orders(self):
        # 清空现有数据
        for item in self.order_tree.get_children():
            self.order_tree.delete(item)

        # 查询当前用户的订单
        self.cursor.execute('''
        SELECT o.order_id, o.order_date, b.title, o.quantity, b.price
        FROM orders o
        JOIN books b ON o.isbn = b.isbn
        WHERE o.subscriber_id = ?
        ORDER BY o.order_date DESC
        ''', (self.current_subscriber['id'],))

        orders = self.cursor.fetchall()

        # 添加到表格
        for order in orders:
            total = order[3] * order[4]
            self.order_tree.insert("", tk.END, values=(
                order[0],
                order[1],
                order[2],
                order[3],
                f"¥{order[4]:.2f}",
                f"¥{total:.2f}"
            ))

    def logout(self):
        self.conn.close()
        self.root.destroy()


if __name__ == "__main__":
    root = tk.Tk()
    app = BookOrderSystem(root)
    root.mainloop()